#include "com_data_dump.h"
#include "ui_com_data_dump.h"

#include <QSettings>
#include <QMessageBox>
#include <QFileDialog>
#include <QSqlError>
#include <QDebug>
#include <QDate>
#include <QNetworkInterface>
#include <QJsonObject>
#include <QJsonDocument>

#include "pub_var.h"

const int SQL_VALUE_COUNT = 50;
const int LOCAL_PORT = 12345;

com_data_dump::com_data_dump(QWidget *parent) :
    QWidget(parent),
    ui(new Ui::com_data_dump)
{
    ui->setupUi(this);

    //restore current windows state
    QSettings setIni("clin.ini", QSettings::IniFormat);
    QRect rct = setIni.value("windowState/data_dump").toRect();
    this->setGeometry(rct);

    //database connect status
    source_connected = false;
    dest_connected = false;

    //dump file directory
    downloadPath = setIni.value("system/downloadPath").toString();

    if(downloadPath.isEmpty()){
        //initial file not contain this value , set a default directory.
        downloadPath = "/0down";
        setIni.setValue("system/downloadPath", "/0down");

    }

    QDir d(downloadPath);

    //filter out invalid character
    downloadPath = d.path();

    //if directory not exists, create it.
    if( ! d.exists() ){
        if(!d.mkdir(downloadPath)){
            QMessageBox::warning(this, "Note", "Directory is invalid, default dir is local disk :\\0down\\");
            downloadPath = "/0down";
            setIni.setValue("system/downloadPath", downloadPath);
            d.setPath(downloadPath);
            if(!d.exists() && !d.mkdir(downloadPath)){
                QMessageBox::warning(this, "Note", "Create default directory failed!");
            }
        }
    }

    ui->pb_dump->setEnabled(false);
    ui->pb_import->setEnabled(false);
    ui->prg_import->setValue(0);
    ui->prg_dump->setValue(0);
    ui->prg_log->setValue(0);

    dlg_delay = new Pub_pop_delay(this);

    qrm_d = NULL;
    qrm_s = NULL;
    qrmt_d = NULL;
    qrmt_s = NULL;
    qro_d = NULL;
    qro_s = NULL;
    qrot_d = NULL;
    qrot_s = NULL;

    qrm_log = new QSqlQuery(pub::db_m);
    qro_log = new QSqlQuery(pub::db_o);
    md_log = new QSqlQueryModel();

    //current_socket = new QTcpSocket(this);
    current_socket = nullptr;

    tm_connect = new QTimer(this);
    tm_connect->setSingleShot(true);
    connect(tm_connect, &QTimer::timeout,
                this, &com_data_dump::handle_connect_timeout);

    tm_connect_count_down = new QTimer(this);
    connect(tm_connect_count_down, &QTimer::timeout,
            this, &com_data_dump::connect_count_down);

    ui->tabWidget->setCurrentIndex(0);

    //获取本机工作IP
    QStringList ips;
    QList<QHostAddress> list = QNetworkInterface::allAddresses();

    foreach (QHostAddress address, list)
    {
        if(address.protocol() == QAbstractSocket::IPv4Protocol)
        {
            //IPv4地址
            ips <<address.toString();
        }
    }

    ui->cbb_local_ip->clear();
    ui->cbb_local_ip->addItems(ips);
    ui->cbb_local_ip->setCurrentIndex(0);
}

com_data_dump::~com_data_dump()
{
    delete ui;
}

void com_data_dump::closeEvent(QCloseEvent *event)
{
    Q_UNUSED(event);
    QSettings setIni("clin.ini", QSettings::IniFormat);
    setIni.setValue("windowState/data_dump", QVariant(this->geometry()));

    emit destroyW( "com_data_dump" );

}

void com_data_dump::on_pb_close_clicked()
{
    close();
}

void com_data_dump::on_pb_import_clicked()
{

    if(ui->le_tbname_d->text().isEmpty())
    {
        QMessageBox::warning(this, "Note", "Destination table name can't be empty！");
        return ;
    }

    //get table all fields
    QMap<QString, QString> fields;

    if(ui->rb_mysql_d->isChecked())
    {
        sq = "select column_name,data_type from information_schema.columns "
             " where table_schema = '%1' and table_name = '%2' order by column_name";

        sq = sq.arg(ui->le_dbname_d->text().trimmed())
                .arg(ui->le_tbname_d->text().trimmed());

        qDebug()<<"check destination table structure sql : "<<sq;

        qrm_d->exec(sq);

        while(qrm_d->next())
        {
            fields[qrm_d->value(0).toString()] = qrm_d->value(1).toString();
        }

        qDebug()<<"dest table structure is : "<<fields;

        if(fields.size() == 0)
        {
            QMessageBox::information(this, "Note", "Destination table name does not exist.");
            return;
        }

        import_table(&fields, qrm_d, "m");
    }
    else
    {
        sq = "select column_name, data_type "
             " from all_tab_columns "
             " where owner = 'RZHIS' "
             " and table_name = '%1'";

        sq = sq.arg(ui->le_tbname_d->text().trimmed().toUpper());

        qDebug()<<"check destination table structure sql : "<<sq;

        qro_d->exec(sq);

        while(qro_d->next())
        {
            fields[qro_d->value(0).toString()] = qro_d->value(1).toString();
        }

        qDebug()<<"table structure is : "<<fields;

        if(fields.size() == 0)
        {
            QMessageBox::information(this, "Note", "Destination able name does not exist.");
            return;
        }
        import_table(&fields, qro_d, "o");
    }

    ui->ckb_remove->setChecked(false);

}

bool com_data_dump::import_table(QMap<QString, QString> *table_fields, QSqlQuery * qr, QString mode)
{

    QStringList field_name, field_type; //field final import
    QStringList input_fields;  //field from input
    QStringList file_fields;  //field from text file
    QString str_input_field;
    QString text_line;  // a line from text file
    QStringList line_data;   // data contain in text_line

    QFile text_file(ui->le_dump_filename->text());

    if(!text_file.open(QIODevice::ReadOnly | QIODevice::Text))
    {
        QMessageBox::warning(this, "Note", "Can't open text file！");
        return false;
    }

    QTextStream txtstrm(&text_file);
    txtstrm.setCodec("UTF-8");

    ui->prg_import->setMaximum(text_file.size());
    qDebug()<<"file_size : "<<QString::number(text_file.size());
    //qDebug()<<"text stream size : "<<QString::number(txtstrm. _file.size());

    //read title line from text file
    str_input_field = txtstrm.readLine();

    //use on progressBar
    //hex character 0x0d 0x0a in every line can't be count, then need manual plus 2
    int char_count = str_input_field.length() + 2;

    str_input_field.replace(" ","");
    //str_input_field.remove(str_input_field.length() - 1 , 1 );
    file_fields = str_input_field.split("\t");

    //fields count in text file, used on determine if text file line is complete.
    int text_fields_count = 0;

    /*
    for(int i = 0; i < file_fields.size() ; i++ )
    {
        if(!file_fields[i].isEmpty())
        {
            text_fields_count++;
        }
    }
    */

    text_fields_count = file_fields.size();

qDebug()<<"text_fields_count:"<<text_fields_count;
qDebug()<<"file_fields.size():"<<file_fields.size();

    //user selected field
    str_input_field = ui->pt_import_fields->toPlainText().trimmed();

    //remove spacial character
    str_input_field.replace("\t"," ");
    str_input_field.replace("\r"," ");
    str_input_field.replace("\n"," ");
    str_input_field.replace(" ","");

    if(str_input_field.isEmpty())
    {
        input_fields = file_fields;
    }
    else
    {
        input_fields = str_input_field.split(",");

        //compare input with text file
        foreach(QString s, input_fields)
        {
            if(!s.isEmpty()
                    && file_fields.indexOf(s) < 0
                    && file_fields.indexOf(s.toUpper()) < 0
                    )
            {
                QMessageBox::warning(this, "Note", "input field \"" + s + "\"not contain in text file！");
                qDebug()<<"s : "<<s;
                return false;
            }
        }
    }

    //compare input field with database
    foreach(QString s, input_fields)
    {
        if(!s.isEmpty() && table_fields->value(s).isEmpty())
        {
            QMessageBox::information(this, "Note", "Database fields does not contain \"" + s + "\"");
            return false;
        }

        field_name << s;
        field_type << table_fields->value(s);
    }

    int fields_count = field_name.size();
    if( fields_count < 1)
    {
        QMessageBox::warning(this, "Note", "Field list is empty ！");
        return false;
    }

    if(ui->ckb_remove->isChecked())
    {
        sq = "delete from " + ui->le_tbname_d->text();
        if(!qr->exec(sq))
        {
            QMessageBox::warning(this, "Error", "Can not remove existed records !");
            return false;
        }
    }

    int line_count = 0;
    int error_count = 0;
    int sql_count = 0;

    while(true)
    {
//qDebug()<<" ";
//qDebug()<<"new line ";
        text_line = "";
        while(true)
        {
            //Perhaps the fields value contain '0x0d 0x0a'

            text_line += txtstrm.readLine();   //read line

            if(txtstrm.atEnd())
            {
                line_data = text_line.split("\t");
                break;
            }

            char_count += text_line.toUtf8().length() +2 ;
            line_data = text_line.split("\t");

            if(line_data.size() < text_fields_count)
            {
                text_line += "\r\n";
            }
            else if(line_data.size() == text_fields_count)
            {
                break;
            }
            else if(line_data.size() > text_fields_count)
            {
                QMessageBox::warning(this, "Error", "Text field structure is invalid ！");
                return false;
            }

        }

        ui->prg_import->setValue(char_count);
        qApp->processEvents();  //display

        if(text_line.isEmpty())
        {
            //end of file

            if(sql_count > 0)
            {
                //there some sqls has not been executed
                if(!qr->exec(sq))
                {
                    qDebug()<<"sql error : "<<qr->lastError().text();
                    qDebug()<<"import data sql : "<<sq;
                    error_count += sql_count;
                }
            }

            break;
        }

        line_count ++ ;

        QString fields, values; //
        int columns = 0;
        bool isFirstField = true; //first field does not need seperate character
        QString sep_char;

        while(true)
        {
            //locate current field index in file fields
            int pos = file_fields.indexOf(field_name[columns]);
            if(pos < 0)
            {
                pos = file_fields.indexOf(field_name[columns].toUpper());
            }

            QString value = line_data[pos];

            if(isFirstField)
            {
                sep_char = "";
                isFirstField = false;
            }
            else
            {
                sep_char = ",";
            }

            if(!field_name[columns].isEmpty())
            {

                fields += sep_char + field_name[columns];
                if(field_type[columns].toUpper() == "DATE")
                {
                    if(mode == "m")
                    {
                        if(value.isEmpty())
                        {
                            values += sep_char + "'0000-00-00'";
                        }
                        else
                        {
                            values += sep_char + "'" + value + "'";
                        }
                    }
                    else if(mode == "o")
                    {
                        if(value.isEmpty())
                        {
                            values += sep_char + "to_date('0001-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss')";
                        }
                        else
                        {
                            values += sep_char + "to_date('" + value + "','yyyy-mm-dd hh24:mi:ss')";
                        }
                    }
                    else
                    {
                        values += sep_char + "'" + value + "'";
                    }
                }
                else if(field_type[columns].toUpper() == "DATETIME")
                {
                    if(value.isEmpty())
                    {
                        values += sep_char + "'0000-00-00 00:00:00'";
                    }
                    else
                    {
                        values += sep_char + "'" + value + "'";
                    }
                }
                else
                {
                    values += sep_char + "'" + value + "'";
                }


            }

            columns++;

            if(columns == fields_count )
            {
                //if current line is complete
                break;
            }
        }

        if(sql_count == 0)
        {
            sq = "insert into "
                    + ui->le_tbname_d->text()
                    + " (" + fields + ") values (" + values + ")";
            sql_count ++;

            if(ui->rb_imp_line->isChecked())
            {
                //single import mode
                if(!qr->exec(sq))
                {
                    qDebug()<<"sql error : "<<qr->lastError().text();
                    qDebug()<<"import data sql : "<<sq;
                    error_count += sql_count;
                }
                sql_count = 0;
            }
        }
        else if(sql_count < SQL_VALUE_COUNT)
        {
            sq += ",(" + values + ")";
            sql_count ++;
        }
        else
        {
            if(!qr->exec(sq))
            {
                qDebug()<<"sql error : "<<qr->lastError().text();
                qDebug()<<"import data sql : "<<sq;
                error_count += sql_count;
            }
            sql_count = 0;

        }

    }

    ui->prg_import->setValue(ui->prg_import->maximum());

    QMessageBox::information(this, "Note", "import complete "
                             + QString::number(line_count)
                             + " lines with "
                             + QString::number(error_count)
                             + " error(s).");

    ui->prg_import->setValue(0);
    return true;
}

void com_data_dump::on_pb_dump_clicked()
{

    if(!source_connected)
    {
        QMessageBox::information(this, "Note", "Source database has not connected!");
        return;
    }

    if(ui->le_tbname_s->text().trimmed().isEmpty())
    {
        QMessageBox::information(this, "Note", "Source table name can't be empty!");
        return;
    }

    QMap<QString, QString> fields;

    if(ui->rb_mysql_s->isChecked())
    {
        sq = "select column_name,data_type from information_schema.columns "
             " where table_schema = '%1' and table_name = '%2' order by column_name";

        sq = sq.arg(ui->le_dbname_s->text().trimmed())
                .arg(ui->le_tbname_s->text().trimmed());

        qDebug()<<"check source table structure sql : "<<sq;

        qrm_s->exec(sq);

        while(qrm_s->next())
        {
            fields[qrm_s->value(0).toString()] = qrm_s->value(1).toString();
        }

        qDebug()<<"table structure is : "<<fields;

        if(fields.size() == 0)
        {
            QMessageBox::information(this, "Note", "Source table name does not exist.");
            return;
        }

        //export data to text file.
        export_text(&fields, qrmt_s, "m");
    }
    else
    {
        sq = "select column_name, data_type "
             " from all_tab_columns "
             " where owner = 'RZHIS' "
             " and table_name = '%1'";

        sq = sq.arg(ui->le_tbname_s->text().trimmed().toUpper());

        qDebug()<<"check source table structure sql : "<<sq;

        qro_s->exec(sq);

        while(qro_s->next())
        {
            fields[qro_s->value(0).toString()] = qro_s->value(1).toString();
        }

        qDebug()<<"table structure is : "<<fields;

        if(fields.size() == 0)
        {
            QMessageBox::information(this, "Note", "Source table name does not exist.");
            return;
        }

        //export data to text file.
        export_text(&fields, qrot_s, "o");
    }
}

bool com_data_dump::export_text(QMap<QString,QString> * fields, QSqlQuery * qr, QString mode)
{

    //QString field_names; //field final export
    QString str_fields_sql;  //field for sql
    QString str_input_field;

    //user selected field
    str_input_field = ui->pt_dump_fields->toPlainText().trimmed();

    //remove spacial character
    str_input_field.replace("\t"," ");
    str_input_field.replace("\r"," ");
    str_input_field.replace("\n"," ");
    str_input_field.replace(" ","");

    if(str_input_field.isEmpty())
    {
        QMap<QString, QString> :: const_iterator it;
        for (it = fields->begin(); it != fields->end(); it++)
        {
            str_fields_sql += it.key() ;
            str_fields_sql += " , ";
        }

    }
    else
    {
        QStringList input_fields = str_input_field.split(",");

        //compare with table fields
        foreach(QString s, input_fields)
        {
            if(!s.isEmpty()
                    && fields->value(s).isEmpty()
                    && fields->value(s.toUpper()).isEmpty())
            {
                QMessageBox::warning(this, "Note", "input field \"" + s + "\"not contain in table！");

                qDebug()<<"s : "<<s;
                qDebug()<<"fields->key(s) : "<<fields->key(s);
                qDebug()<<"fields->value(s) : "<<fields->value(s);
                qDebug()<<"fields->value(s).toUpper() : "<<fields->value(s.toUpper());

                return false;
            }
            str_fields_sql += s ;
            str_fields_sql += " , ";
        }
    }

    str_fields_sql = str_fields_sql.left(str_fields_sql.length() - 3);

    QString sql;

    //final text file will not import through "load data in local file",
    //so no need to remove last "\t", it has no effect to text file.

    //temporary file
    QString tmp_file_name = downloadPath + "/" +  ui->le_tbname_s->text().trimmed() + ".txt";
    QFile f;

    if(f.exists(tmp_file_name))
    {
        if(!f.remove(tmp_file_name))
        {
            QMessageBox::warning(this, "Note", "Can't remove existed file!");
            return false;
        }
    }

    QFile tmp_file(tmp_file_name);

    if(!tmp_file.open(QIODevice::WriteOnly))
    {
        QMessageBox::information(this, "Warning", "Create temporary file failed!");
        return false;
    }

    dlg_delay->setText("Prepare database to export ... ");
    dlg_delay->setModal(true);
    dlg_delay->setNeedClose(false);
    dlg_delay->show();
    qApp->processEvents();

    sql = "select " + str_fields_sql + " from " + ui->le_tbname_s->text();

    //if has constrain clause
    if(!ui->pt_dump_constrain->toPlainText().trimmed().isEmpty())
    {
        sql += " where " + ui->pt_dump_constrain->toPlainText();
    }

    QString str = str_fields_sql.replace(" , ", "\t") + "\t\r\n";

    qDebug()<<"export SQL : "<<sql;

    // plainTextEdit may be input some spacial character
    sql.replace("\t"," ");
    sql.replace("\r"," ");
    sql.replace("\n"," ");

    QStringList field_list = str_fields_sql.split("\t");
qDebug()<<"field_list : "<<field_list;
    if(!qr->exec(sql))
    {
        QMessageBox::information(this, "Warning", "Export SQL execute error!");
        return false;
    }

    qr->last();
    ui->prg_dump->setMaximum(qr->at() + 1);
    qr->first();

    dlg_delay->setNeedClose(true);
    dlg_delay->closeWindow();

    int rec_count = 0;
    //QMap<QString,QString>::const_iterator it;

    while(true)
    {

        rec_count++;
        ui->prg_dump->setValue(rec_count);
        qApp->processEvents();

        //for(it = fields->begin(); it != fields->end(); it ++)
        for(int i = 0 ; i< field_list.size(); i++)
        {
//            if( str_fields_sql.indexOf(it.key()) < 0)
//            {
//                //the field no need export
//                continue;
//            }

            QString field_type = fields->value(field_list[i]).toUpper();

            if( field_type == "DATE" )
            {
                if(mode == "m")
                {
                    str += qr->value(field_list[i]).toDate().toString("yyyy-MM-dd");
                }
                else if(mode == "o")
                {
                    str += qr->value(field_list[i]).toDateTime().toString("yyyy-MM-dd hh:mm:ss");
                }
            }
            else if( field_type == "DATETIME" )
            {
            }
            else
            {
                //text file seperate character is "\t"
                str += qr->value(field_list[i]).toString().replace("\t"," ");
            }

            str += "\t";
        }

        str += "\r\n";
        tmp_file.write(str.toUtf8());
        str.clear();

        if(!qr->next())
        {
            break;
        }
    }

    tmp_file.close();

    QMessageBox::information(this, "Note", QString::number(ui->prg_dump->maximum())
                             + " records exported successful at " + tmp_file_name);

    ui->prg_dump->setValue(0);

    return  true;

}

void com_data_dump::on_pb_connect_s_clicked()
{

    source_connected = false;  //pre-set connected to false
    ui->pb_dump->setEnabled(false);

    QString ip = ui->le_ip_s->text().trimmed();
    QString port = ui->le_port_s->text().trimmed();
    QString dbnm = ui->le_dbname_s->text().trimmed();
    QString usnm = ui->le_username_s->text().trimmed();
    QString pswd = ui->le_pswd_s->text().trimmed();

    if(ip.isEmpty() || port.isEmpty() || dbnm.isEmpty() || usnm.isEmpty() || pswd.isEmpty())
    {
        QMessageBox::information(this, "Note", "ip, port, dbname, username, pswd can't be empty!");
        return;
    }

    if(ui->rb_mysql_s->isChecked())
    {
        db_m_s = QSqlDatabase::addDatabase("QMYSQL","db_m_s");
        db_m_s.setHostName(ip);
        db_m_s.setPort(port.toInt());
        db_m_s.setDatabaseName(dbnm);
        db_m_s.setUserName(usnm);
        db_m_s.setPassword(pswd);
        if(!db_m_s.open()){
            QString tmp = "Can not open database ！ \nPort: " + ui->le_port_s->text();
            tmp += "\nHost: " + ui->le_ip_s->text();
            tmp += "\nDBName: " + ui->le_dbname_s->text();
            QMessageBox::information(NULL,"注意",tmp);
            return;
        }

        if(qrm_s != NULL)  delete qrm_s;
        if(qrmt_s != NULL)  delete qrmt_s;

        qrm_s = new QSqlQuery(db_m_s);
        qrmt_s = new QSqlQuery(db_m_s);

    }
    else
    {
        db_o_s = QSqlDatabase::addDatabase("QOCI","db_o_s");
        db_o_s.setHostName(ip);
        db_o_s.setPort(port.toInt());
        db_o_s.setDatabaseName(dbnm);
        db_o_s.setUserName(usnm);
        db_o_s.setPassword(pswd);
        if(!db_o_s.open()){
            QString tmp = "Can not open database ！ \nPort: " + ui->le_port_s->text();
            tmp += "\nHost: " + ui->le_ip_s->text();
            tmp += "\nDBName: " + ui->le_dbname_s->text();
            QMessageBox::information(NULL,"注意",tmp);
            return;
        }

        if(qro_s != NULL)  delete qro_s;
        if(qrot_s != NULL)  delete qrot_s;

        qro_s = new QSqlQuery(db_o_s);
        qrot_s = new QSqlQuery(db_o_s);

    }

    ui->pb_dump->setEnabled(true);
    source_connected = true;

    QMessageBox::information(NULL,"Note", "Source database connected!");

}

void com_data_dump::on_pb_connect_d_clicked()
{
    dest_connected = false;  //pre-set connected to false
    ui->pb_import->setEnabled(false);

    QString ip = ui->le_ip_d->text().trimmed();
    QString port = ui->le_port_d->text().trimmed();
    QString dbnm = ui->le_dbname_d->text().trimmed();
    QString usnm = ui->le_username_d->text().trimmed();
    QString pswd = ui->le_pswd_d->text().trimmed();

    if(ip.isEmpty() || port.isEmpty() || dbnm.isEmpty() || usnm.isEmpty() || pswd.isEmpty())
    {
        QMessageBox::information(this, "Note", "ip, port, dbname, username, pswd can't be empty!");
        return;
    }

    if(ui->rb_mysql_d->isChecked())
    {
        db_m_d = QSqlDatabase::addDatabase("QMYSQL","db_m_d");
        db_m_d.setHostName(ip);
        db_m_d.setPort(port.toInt());
        db_m_d.setDatabaseName(dbnm);
        db_m_d.setUserName(usnm);
        db_m_d.setPassword(pswd);
        if(!db_m_d.open()){
            QString tmp = "Can not open database ！ \nPort: " + ui->le_port_d->text();
            tmp += "\nHost: " + ui->le_ip_d->text();
            tmp += "\nDBName: " + ui->le_dbname_d->text();
            QMessageBox::information(NULL,"注意",tmp);
            return;
        }

        if(qrm_d != NULL)  delete qrm_d;
        if(qrmt_d != NULL)  delete qrmt_d;

        qrm_d = new QSqlQuery(db_m_d);
        qrmt_d = new QSqlQuery(db_m_d);

    }
    else
    {
        db_o_d = QSqlDatabase::addDatabase("QOCI","db_o_d");
        db_o_d.setHostName(ip);
        db_o_d.setPort(port.toInt());
        db_o_d.setDatabaseName(dbnm);
        db_o_d.setUserName(usnm);
        db_o_d.setPassword(pswd);
        if(!db_o_d.open()){
            QString tmp = "Can not open database ！ \nPort: " + ui->le_port_d->text();
            tmp += "\nHost: " + ui->le_ip_d->text();
            tmp += "\nDBName: " + ui->le_dbname_d->text();
            QMessageBox::information(NULL,"注意",tmp);
            return;
        }

        if(qro_d != NULL)  delete qro_d;
        if(qrot_d != NULL)  delete qrot_d;

        qro_d = new QSqlQuery(db_o_d);
        qrot_d = new QSqlQuery(db_o_d);

    }

    dest_connected = true;
    ui->pb_import->setEnabled(true);

    QMessageBox::information(NULL,"Note", "Destination database connected!");
}

void com_data_dump::on_tb_openfile_clicked()
{

    if(!dest_connected)
    {
        QMessageBox::warning(this, "Note", "Database connection is invalid！");
        return ;
    }

    ui->le_dump_filename->clear();
    ui->le_tbname_d->clear();

    QString fileName = QFileDialog::getOpenFileName(this, "Open file to import", downloadPath,  "(*.txt)",  0);

    if(fileName.isNull()){
        return;
    }

    ui->le_dump_filename->setText(fileName);

    QString  pureFileName, table_name;

    QFileInfo fileinfo = QFileInfo(fileName);

    pureFileName = fileinfo.fileName();
    table_name = pureFileName.left(pureFileName.length() - 4);

    ui->le_tbname_d->setText(table_name);

    QString fields;

    if(ui->rb_mysql_d->isChecked())
    {
        sq = "select column_name, data_type "
             " from information_schema.columns "
             " where table_schema = '%1' and table_name = '%2' "
             " order by column_name";

        sq = sq.arg(ui->le_dbname_d->text().trimmed())
                .arg(ui->le_tbname_d->text().trimmed());

        qrm_d->exec(sq);

        while(qrm_d->next())
        {
            fields += qrm_d->value(0).toString() + " , ";
        }

    }
    else
    {
        sq = " select column_name, data_type "
             " from all_tab_columns "
             " where owner = 'RZHIS' and table_name = '%1'"
             " order by column_name ";

        sq = sq.arg(ui->le_tbname_d->text().trimmed().toUpper());

        qro_d->exec(sq);

        while(qro_d->next())
        {
            fields += qro_d->value(0).toString() + " , ";
        }
    }

    ui->pt_fields_db->setPlainText(fields.left(fields.length() - 3));

    if(ui->pt_fields_db->toPlainText().trimmed().isEmpty())
    {
        QMessageBox::warning(this, "Note", "Destination database does not contain table name '"
                             + ui->le_tbname_d->text().trimmed().toUpper() + "' ");
        return ;
    }

    QFile text_file(ui->le_dump_filename->text());

    if(!text_file.open(QIODevice::ReadOnly | QIODevice::Text))
    {
        QMessageBox::warning(this, "Note", "Can't open text file！");
        return ;
    }

    QTextStream txtstrm(&text_file);
    txtstrm.setCodec("UTF-8");

    //read title line from text file
    QString str = txtstrm.readLine();

    //replace tablespace to comma, then replace last comma to space
    str.replace("\t", " , ");
    str.replace(str.length() - 3 , 3 , "");

    ui->pt_fields_txt->setPlainText(str);

}

void com_data_dump::on_pb_get_fields_clicked()
{
    if(ui->le_tbname_s->text().trimmed().isEmpty())
    {
        QMessageBox::warning(this, "Note", "Table name can't be empty ！");
        return ;
    }

    if(!source_connected)
    {
        QMessageBox::warning(this, "Note", "Please connect database first ！");
        return ;
    }

    QString fields;

    if(ui->rb_mysql_s->isChecked())
    {
        sq = "select column_name, data_type "
             " from information_schema.columns "
             " where table_schema = '%1' and table_name = '%2' "
             " order by column_name";

        sq = sq.arg(ui->le_dbname_s->text().trimmed())
                .arg(ui->le_tbname_s->text().trimmed());

        qrm_s->exec(sq);

        while(qrm_s->next())
        {
            fields += qrm_s->value(0).toString() + " , ";
        }
    }
    else
    {
        sq = "select column_name, data_type "
             " from all_tab_columns "
             " where owner = 'RZHIS' and table_name = '%1'"
             " order by column_name ";

        sq = sq.arg(ui->le_tbname_s->text().trimmed().toUpper());

        qro_s->exec(sq);

        while(qro_s->next())
        {
            fields += qro_s->value(0).toString() + " , ";
        }
    }
    if(fields.isEmpty())
    {
        QMessageBox::information(this, "Note", "Table name is invalid.");
        return;
    }

    ui->pt_dump_fields->setPlainText(fields.left(fields.length() - 3));

}

void com_data_dump::on_pb_get_local_d_clicked()
{
    //get initial file infomation
    QSettings setIni("clin.ini", QSettings::IniFormat);
    QString host,port,dbnm, usr, pswd;

    if(ui->rb_mysql_d->isChecked())
    {
        //MYSQL
        host = setIni.value("database/m_hostname").toString();
        port = setIni.value("database/m_port").toString();
        dbnm = setIni.value("database/m_databasename").toString();
        usr = setIni.value("database/m_username").toString();
        pswd = setIni.value("database/m_password").toString();
    }
    else
    {
        //ORACLE
        host = setIni.value("database/o_hostname").toString();
        port = setIni.value("database/o_port").toString();
        dbnm = setIni.value("database/o_databasename").toString();
        usr = setIni.value("database/o_username").toString();
        pswd = setIni.value("database/o_password").toString();
    }

    ui->le_ip_d->setText(host);
    ui->le_port_d->setText(port);
    ui->le_dbname_d->setText(dbnm);
    ui->le_username_d->setText(usr);
    ui->le_pswd_d->setText(pswd);

}

void com_data_dump::on_pb_get_local_s_clicked()
{
    //get initial file infomation
    QSettings setIni("clin.ini", QSettings::IniFormat);
    QString host,port,dbnm, usr, pswd;

    if(ui->rb_mysql_s->isChecked())
    {
        //MYSQL
        host = setIni.value("database/m_hostname").toString();
        port = setIni.value("database/m_port").toString();
        dbnm = setIni.value("database/m_databasename").toString();
        usr = setIni.value("database/m_username").toString();
        pswd = setIni.value("database/m_password").toString();
    }
    else
    {
        //ORACLE
        host = setIni.value("database/o_hostname").toString();
        port = setIni.value("database/o_port").toString();
        dbnm = setIni.value("database/o_databasename").toString();
        usr = setIni.value("database/o_username").toString();
        pswd = setIni.value("database/o_password").toString();
    }

    ui->le_ip_s->setText(host);
    ui->le_port_s->setText(port);
    ui->le_dbname_s->setText(dbnm);
    ui->le_username_s->setText(usr);
    ui->le_pswd_s->setText(pswd);

}

// retrieve log file

void com_data_dump::on_pb_fresh_online_user_clicked()
{
    qDebug()<<"\n刷新在线用户列表\n";
    sq = " select empl_name, dept_name, log_machine, "
         " log_address, login_date, empl_code "
         " from com_loginlog "
         " where login_date > trunc(sysdate) "
         " and logout_date is null "
         " order by login_date desc";

    md_log->setQuery(sq, pub::db_o);
    ui->tv_user_list->setModel(md_log);
    int w = ui->tv_user_list->width();
    pub::setColumnWidth(ui->tv_user_list, "0.1^0.15^0.25^0.2^0.25", w);

    qDebug()<<sq<<"\n";
}

void com_data_dump::on_tv_user_list_clicked(const QModelIndex &index)
{
    Q_UNUSED(index)
    ui->lw_file_list->clear();
}

void com_data_dump::on_tv_user_list_doubleClicked(const QModelIndex &index)
{
    Q_UNUSED(index)
    on_tv_user_list_clicked(index);
}

void com_data_dump::on_pb_download_clicked()
{
    qDebug()<<"\n\non_pb_download_clicked 开始\n";
    QModelIndex idx = ui->tv_user_list->currentIndex();

    if(!idx.isValid())
    {
        QMessageBox::information(this, "Note", "Please get a dest user first !");
        return;
    }

    QString dest = idx.siblingAtColumn(5).data().toString();
    QString ip = idx.siblingAtColumn(3).data().toString();
    qDebug()<<"dest,ip  : "<<dest << " , "<<ip<<"\n";

    if(ui->lw_file_list->selectedItems().isEmpty())
    {
        QMessageBox::critical(this, "Error", "Please select a file first.");
        return;
    }

    tm_connect_count_down->start(1000);
    connect_count = 0;

    QString f = ui->lw_file_list->currentItem()->text();

    QString flnm = pub::getStr(f, "^", 1);
    remote_file_size = pub::getStr(f, "^", 2).toInt();
    bytesReceived = 0;

    qDebug()<<"remote_file_name : "<<flnm<<"\n";

    QStringList lst = flnm.split("/");
    local_file_name = ip + "-" + lst[lst.size()-1];

    qDebug()<<"local_file_name  : "<<local_file_name<<"\n";

    file.setFileName(pub::downloadPath + "/" + local_file_name);

    if (file.exists())
    {
        file.close();
        file.remove();
    }

    if (!file.open(QIODevice::WriteOnly))
    {
        QMessageBox::critical(this, "Error", "Cannot open local file for writing");
        return;
    }
    else
    {
        tm_connect->start(4000);
        qDebug()<<"file open normal, timer start.\n";
    }

    remote_file_name = flnm;


    if(tm_connect->isActive())
    {
        tm_connect->stop();
    }

    if(tm_connect_count_down->isActive())
    {
        tm_connect_count_down->stop();
    }

    if(current_socket != nullptr && current_socket->isValid())
    {
        current_socket->disconnectFromHost();
        current_socket->deleteLater();
    }

    current_socket = new QTcpSocket(this);

    current_socket->connectToHost(ip, LOCAL_PORT);

    connect(current_socket, &QTcpSocket::connected,
            this, &com_data_dump::send_file_apply);

    connect(current_socket, &QTcpSocket::readyRead,
            this, &com_data_dump::get_file);

    speedTimer.restart();
    elapsed_second = 0;

}

void com_data_dump::send_file_apply()
{

    if(tm_connect_count_down->isActive())
    {
        tm_connect_count_down->stop();
    }

    tm_connect->start(3000);

    QJsonObject ob;

    ob.insert("dest", "file");
    ob.insert("name", remote_file_name);

    QJsonDocument doc;
    doc.setObject(ob);

    QByteArray bt;// = doc.toJson(QJsonDocument::Compact);

    QDataStream stream(&bt, QIODevice::WriteOnly);

    stream << doc.toJson(QJsonDocument::Compact); // 序列化到字节数组

    qDebug()<<"doc,stream"<<"\n"
           <<doc<<"\n"
          <<stream<<"\n"
            ;

    qint64 bytes = current_socket->write(bt);

    if (bytes == -1)
    {
        qDebug()<<"tcp socket , get_file_apply 写入错误\n";
    }
    else
    {
        qDebug()<<"tcp socket , get_file_apply 写入字节 "<<QString::number(bytes)<<"\n";
    }

}

void com_data_dump::get_file()
{
    //QDataStream in(current_socket);
    //in.setVersion(QDataStream::Qt_5_12);

    qint64 elapsed = speedTimer.elapsed();

    if (elapsed/1000 > elapsed_second)
    {

        elapsed_second++;

        QString unit = " Byte/s";

        double speed = double(bytesReceived) / elapsed_second;

        if(speed > 1024)
        {
            speed /= 1024;
            unit = " KB/s";
        }

        if(speed > 1024)
        {
            speed /= 1024;
            unit = " MB/s";
        }

        ui->statusLabel->setText("Speed: " + QString::number(speed, 'f', 1) + unit);

    }

    QByteArray buffer;
    if (bytesReceived < remote_file_size)
    {
        tm_connect->start(3000);

        buffer = current_socket->readAll();
        file.write(buffer);

        bytesReceived += buffer.size();
        ui->prg_log->setValue(static_cast<int>(bytesReceived
                                               / static_cast<double>(remote_file_size) * 100));

        if (bytesReceived >= remote_file_size)
        {
            if(tm_connect->isActive())
            {
                tm_connect->stop();
            }

            file.close();

            qDebug()<<"get_file finish , file closed.\n";

            ui->statusLabel->setText("File received: "
                                     + pub::downloadPath
                                     + "/" + local_file_name);
            current_socket->disconnectFromHost();
            current_socket->deleteLater();
            current_socket = nullptr;
            ui->prg_log->setValue(0);
        }
        else
        {
            //qDebug()<<"get_file not finish , file is openning.\n";
        }
    }
    else
    {
        qDebug()<<"get file, bytesReceived >= remote_file_size\n";
    }
}

void com_data_dump::read_file_list()
{

    qDebug()<<"QTcpSocket::readyRead, read_file_list";

    if(tm_connect_count_down->isActive())
    {
        qDebug()<<"tm_connect_count_down->stop";
        tm_connect_count_down->stop();
    }

    btar_file_list += current_socket->readAll();

    if(is_first_read_file_list)
    {
        is_first_read_file_list = false;

        QDataStream in(btar_file_list );

        in >> file_list_size;

        qDebug()<<"file_list_size is : "<<QString::number(file_list_size);

    }

    qDebug()<<"received btar_file_list size is : "<<QString::number(btar_file_list.size());

    if(btar_file_list.size() >= file_list_size)
    {

        qDebug()<<"received files list finish. ";

        QDataStream in(btar_file_list );

        QStringList file_list;

        in >> file_list_size;
        in >> file_list;
        ui->lw_file_list->addItems(file_list);

        if(tm_connect->isActive())
        {
            qDebug()<<"tm_connect->stop";
            tm_connect->stop();
        }

        qDebug()<<"current_socket disconnect from host.\n";
        current_socket->disconnectFromHost();
        current_socket->deleteLater();
        current_socket = nullptr;
    }

}

void com_data_dump::handle_connect_timeout()
{

    qDebug()<<"\n\nhandle_connect_timeout() \n";

    ui->statusLabel->setText("Connect time out.");
    if(tm_connect_count_down->isActive())
    {
        tm_connect_count_down->stop();
    }

    if (current_socket == nullptr)
    {
        qDebug()<<"current_socket == nullptr\n";
    }
    else
    {
        switch (current_socket->state()) {
        case QAbstractSocket::UnconnectedState:
            qDebug() << "Unconnected";
            break;
        case QAbstractSocket::HostLookupState:
            qDebug() << "Host lookup in progress";
            break;
        case QAbstractSocket::ConnectingState:
            qDebug() << "Connecting";
            break;
        case QAbstractSocket::ConnectedState:
            qDebug() << "Connected";
            current_socket->disconnectFromHost();
            break;
        case QAbstractSocket::BoundState:
            qDebug() << "Bound";
            break;
        case QAbstractSocket::ListeningState:
            qDebug() << "Listening";
            break;
        case QAbstractSocket::ClosingState:
            qDebug() << "Closing";
            break;
        default:
            qDebug() << "Unknown state";
            break;
        }

        qDebug()<<"destroy current socket. \n";
        current_socket->deleteLater();
        current_socket = nullptr;
    }
}

void com_data_dump::connect_count_down()
{
    connect_count++;
    ui->statusLabel->setText("Listening..." + QString::number(connect_count));
}

void com_data_dump::on_pb_get_file_list_clicked()
{

    if(tm_connect->isActive())
    {
        qDebug()<<"tm_connect->stop";
        tm_connect->stop();
    }

    if(tm_connect_count_down->isActive())
    {
        qDebug()<<"tm_connect_count_down->stop";
        tm_connect_count_down->stop();
    }

    if(current_socket != nullptr && current_socket->isValid())
    {
        qDebug()<<"current_socket != nullptr, disconnectFromHost, deleteLater";
        current_socket->disconnectFromHost();
        current_socket->deleteLater();
    }

    qDebug()<<"connect and count_down timer start.";
    tm_connect->start(3000);
    tm_connect_count_down->start(1000);
    connect_count = 0;

    QModelIndex idx = ui->tv_user_list->currentIndex();

    if(!idx.isValid())
    {
        QMessageBox::information(this, "Note", "Please get a dest user first !");
        return;
    }

    QString dest = idx.siblingAtColumn(5).data().toString();
    QString ip = idx.siblingAtColumn(3).data().toString();

    qDebug()<<"dest,ip  : "<<dest << " , "<<ip<<"\n";

    btar_file_list.clear();
    is_first_read_file_list = true;

    current_socket = new QTcpSocket(this);

    current_socket->connectToHost(ip, LOCAL_PORT);

    connect(current_socket, &QTcpSocket::connected, this, [this]() {

        qDebug()<<"current_socket connected.\n";

        QJsonObject ob;

        ob.insert("dest", "dir");
        ob.insert("dir_mode", "log");

        QJsonDocument doc;
        doc.setObject(ob);

        QByteArray bt;// = doc.toJson(QJsonDocument::Compact);

        QDataStream stream(&bt, QIODevice::WriteOnly);

        stream << doc.toJson(QJsonDocument::Compact); // 序列化到字节数组

        qint64 bytes = current_socket->write(bt);

        if (bytes == -1)
        {
            qDebug()<<"tcp socket 写入错误\n";
        }
        else
        {
            qDebug()<<"tcp socket 写入字节 "<<QString::number(bytes)<<"\n";
        }

    });

    connect(current_socket, &QTcpSocket::readyRead,
            this, &com_data_dump::read_file_list);

}

